# -*- encoding:utf-8 -*-

import os
import csv, codecs
from openpyxl import load_workbook,workbook


class ExcelImpl:

    def __init__(self,path=None):
        self.count=0
        if path==None:
            self.TilebuilderTestcasefilepath = os.getcwd() + "\\inputdata\\TestCase.xlsx"
        else:
            self.TilebuilderTestcasefilepath = path

        try:
            self.book = load_workbook(self.TilebuilderTestcasefilepath)
        except Exception as e:
            print UnboundLocalError, e

    def getTestCaseByExcel(self,book=None,type=None):
        if book==None:
            book=self.book

        listRes = []

        if type=='TileBuilder':
            work_sheet = book.get_sheet_by_name(u'TileBuilder')

        if type=='PreProcessor':
            work_sheet = book.get_sheet_by_name(u'PreProcessor')

        if type == 'DeuGlobeExplorer':
            work_sheet = book.get_sheet_by_name(u'DeuGlobeExplorer')

        for r in range(2, work_sheet.max_row+1):
            listAdd = []
            for c in range(1, work_sheet.max_column+1):
                val = work_sheet.cell(row=r,column=c).value
                if val != None:
                    listAdd.append(val)
            listRes.append(listAdd)
        return listRes


    def getOneTestCaseByExcel(self,book=None,type=None,testNo=None):
        if book==None:
            book=self.book

        listRes = []

        if type == 'TileBuilder' or type == None:
            work_sheet = book.get_sheet_by_name(u'TileBuilder')

        if type=='PreProcessor':
            work_sheet = book.get_sheet_by_name(u'PreProcessor')

        if type == 'DeuGlobeExplorer':
            work_sheet = book.get_sheet_by_name(u'DeuGlobeExplorer')

        for r in range(2, work_sheet.max_row+1):

            if work_sheet.cell(row=r,column=1).value==testNo:
                for c in range(1,work_sheet.max_column+1):
                    val = work_sheet.cell(row=r,column=c).value
                    if val != None:
                        listRes.append(val)
        return listRes

    def getFilePath(self,book=None,type=None):
        if book==None:
            book=self.book

        table = book.get_sheet_by_name(u'FilePath')

        for i in range(1,table.max_row+1):
            if type == table.cell(row=i,column=2).value: #table.cell(i,1).value:
                return table.cell(row=i,column=3).value

    def writeStatusSheet(self,testCaseName=None,testStatus='failed'):

        eri = ExcelReadImpl()

        n=0
        book = self.book

        try:
            work_sheet=book.get_sheet_by_name("Reslut")
        except:
            n=1

        if n==1:
            book.create_sheet("Reslut")
            work_sheet=book.get_sheet_by_name("Reslut")

        # 清空
        # print self.count
        if self.count==0:
            self.cleanSheet(work_sheet)

        maxrow = eri.get_rows_count(work_sheet)

        work_sheet.cell(row=maxrow, column=1).set_explicit_value(value=str(testCaseName))
        work_sheet.cell(row=maxrow, column=2).set_explicit_value(value=str(testStatus))

        self.book.save(self.TilebuilderTestcasefilepath)

    def write_process_info_to_csv(self, datatype="cpu", data=None):
        # os.chdir("..")
        outpath = os.getcwd() + "\\outputdata\\OutExcel\\"
        # outpath = "D:\\DMWorkSpace\\PYWorkSpace\\DeuToolsGUITestPY3\\outputdata\\OutExcel\\"

        if datatype == "cpu":
            filepath = outpath + data[0] + "_CpuInfo.csv"
        else:  # 内存
            filepath = outpath + data[0] + "_MenInfo.csv"

        try:
            csvfile = file(filepath, 'ab+')
        except Exception as e:
            print Exception, e
            csvfile = file(filepath, 'wb+')

        csvfile.write(codecs.BOM_UTF8)
        writer = csv.writer(csvfile)
        writer.writerow(data)
        csvfile.close()

    def write_process_info_to_sheet(self, datatype="cpu", data=None):
        """
        把内存、CPU写入EXCEL表中去
        :param datatype: 表示CPU或是内存数据
        :param data:返回的是LIST数据
        :return:返回为空
        """
        # outpath = os.getcwd()+"\\OutExcel\\"
        outpath = "D:\\DMWorkSpace\\PYWorkSpace\\DeuToolsGUITestPY3\\outputdata\\OutExcel\\"

        eri = ExcelReadImpl()

        if datatype == "cpu":
            filepath = outpath + "CpuInfo.xlsx"
            book = load_workbook(filepath)

            book.create_sheet("CpuInfo")
            work_sheet = book.get_sheet_by_name("CpuInfo")

        else:  # 内存
            filepath = outpath + "MenInfo.xlsx"
            book = load_workbook(filepath)

            book.create_sheet("MenInfo")
            work_sheet = book.get_sheet_by_name("MenInfo")

            # self.cleanSheet(work_sheet)

        maxrow = eri.get_rows_count(work_sheet)

        for i in range(0, len(data)):
            work_sheet.cell(row=maxrow, column=i + 1).set_explicit_value(value=str(data[i]))

        book.save(filepath)

    def cleanSheet(self, workSheet=None, newPathfile=None):
        for r in range(2, workSheet.max_row+1):
            for c in range(2, workSheet.max_column+1):
                workSheet.cell(row=r, column=c).set_explicit_value(value=None,data_type="n")
        self.count=1
        if workSheet == None:
            self.book.save(self.TilebuilderTestcasefilepath)
        elif newPathfile != None:
            self.book.save(newPathfile)


class ExcelReadImpl:
    def get_rows_count(self,worksheet):
        for i in range(1,65535):
            val = worksheet.cell(row=i,column=1).value
            if val==None:
                return i

    def get_col_count(self,worksheet):
        for i in range(1,65535):
            val = worksheet.cell(row=1,column=i).value
            if val==None:
                return i


class ExcelReadObject():
    def __init__(self):
        self.ei = ExcelImpl()

    def getHostIP(self):
        return self.ei.getFilePath(type="HostIP")

# unit test1
if __name__ == '__main__':
    # ei = ExcelImpl(path=r'TestCase.xlsx')
    # ei.getTestCaseByExcel(type=r'TileBuilder')
    # ei.ceateSheet("用例名称","状态")
    # ero = ExcelReadObject()
    # print(ero.write_process_info_to_sheet())
    ei = ExcelImpl(r"D:\DMWorkSpace\PYWorkSpace\DeuToolsGUITestPY3\inputdata\TestCase.xlsx")
    ei.write_process_info_to_csv("cpu", ["test1111", 1, 1, 1.21, "91%", "hhhhhhh"])
